﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using EntityObjects;

namespace DAL_V1.App_Code.DBBroker
{
    public class CatalogueDBBroker
    {
        private SSISDataSet ds;
        private SSISDataSetTableAdapters.CatalogueTableAdapter tda;
        private SSISDataSetTableAdapters.CategoryTableAdapter categorytda;
        private int index;
        private List<Catalogue> catal_list;
        private List<Category> categ_list;        
        private SSISDataSet.CategoryDataTable category_td;
        private SSISDataSet.CatalogueDataTable catalogue_td;

        //singleton
        private static bool instance_flag = false;
        private static CatalogueDBBroker instance;


        private CatalogueDBBroker()
        {
            ds = new SSISDataSet();
            tda = new SSISDataSetTableAdapters.CatalogueTableAdapter();
            categorytda = new SSISDataSetTableAdapters.CategoryTableAdapter();
            tda.Fill(ds.Catalogue);
            categorytda.Fill(ds.Category);
        }

        public static CatalogueDBBroker getCatalogueDBBroker()
        {
            if (!instance_flag)
            {
                instance = new CatalogueDBBroker();
                instance_flag = true;
                
                return instance;
            }
            else
            {
                return instance;
            }
        }

        public List<Catalogue> selectAllCatalogue()
        {
            catal_list = new List<Catalogue>();
            
            foreach (SSISDataSet.CatalogueRow row in ds.Catalogue.Rows)
            {
                Catalogue catal = new Catalogue();
                catal.ItemId = row.ItemId;
                catal.CategoryId = row.CategoryId;
                catal.Description = row.Description;
                catal.BinId = row.BinId;
                catal.ReOrderLevel = row.ReOrderLevel;
                catal.ReOrderQty = row.ReOrderQty;
                catal.UOM = row.UOM;
                catal.TotalQty = row.TotalQty;
                catal_list.Add(catal);
            }
            return catal_list;
        }

        //return data table
        public DataTable selectAllCatalogue_DT()
        {
            return ds.Catalogue;
        }
             

        public int addCatalogue(Catalogue catelogue)
        {
            SSISDataSet.CatalogueRow newRow = ds.Catalogue.NewCatalogueRow();
            newRow.ItemId = catelogue.ItemId;
            newRow.CategoryId = catelogue.CategoryId;
            newRow.Description = catelogue.Description;
            newRow.BinId = catelogue.BinId;
            newRow.ReOrderLevel = catelogue.ReOrderLevel;
            newRow.ReOrderQty = catelogue.ReOrderQty;
            newRow.UOM = catelogue.UOM;
            newRow.TotalQty = catelogue.TotalQty;
            ds.Catalogue.Rows.Add(newRow);
            return tda.Update(ds.Catalogue);
        }

        //private int getIndex(String Id)
        //{
        //    SSISDataSet.CatalogueRow row = ds.Catalogue.FindByItemId(Id);
        //    return ds.Catalogue.Rows.IndexOf(row);
        //}

        private SSISDataSet.CatalogueRow getRowById(String id)
        {
            SSISDataSet.CatalogueRow r = ds.Catalogue.FindByItemId(id);
            return r;
        }

        public int updateCatalogue(Catalogue catalogue, String Id)
        {
            SSISDataSet.CatalogueRow row = getRowById(Id);
            if (row != null)
            {
                row.ItemId = catalogue.ItemId;
                row.CategoryId = catalogue.CategoryId;
                row.Description = catalogue.Description;
                row.BinId = catalogue.BinId;
                row.ReOrderLevel = catalogue.ReOrderLevel;
                row.ReOrderQty = catalogue.ReOrderQty;
                row.UOM = catalogue.UOM;
                row.TotalQty = catalogue.TotalQty;
            }

            return tda.Update(ds.Catalogue);
        }

        public int deleteCatalogue(String Id)
        {
            SSISDataSet.CatalogueRow row = getRowById(Id);
            if (row != null)
            {
                ds.Catalogue.Rows.Remove(row);
            }
            return tda.Update(ds.Catalogue);

        }

        public Catalogue selectCatalogue(String Id)
        {
            SSISDataSet.CatalogueRow r = getRowById(Id);
            if (r != null)
            {
                Catalogue catal = new Catalogue();
                catal.ItemId = r.ItemId;
                catal.CategoryId = r.CategoryId;
                catal.Description = r.Description;
                catal.BinId = r.BinId;
                catal.ReOrderLevel = r.ReOrderLevel;
                catal.ReOrderQty = r.ReOrderQty;
                catal.UOM = r.UOM;
                return catal;
            }
            else
            {
                return null;
            }
        }

        public List<Catalogue> selectCatalogueByCategoryId(int Id)
        {
            catalogue_td = tda.GetDataByCategoryId(Id);

            if (catalogue_td != null)
            {
                catal_list = new List<Catalogue>();
                for (int i = 0; i < catalogue_td.Rows.Count; i++)
                {
                    Catalogue catal = new Catalogue();
                    catal.ItemId = catalogue_td[i].ItemId;
                    catal.CategoryId = catalogue_td[i].CategoryId;
                    catal.Description = catalogue_td[i].Description;
                    catal.BinId = catalogue_td[i].BinId;
                    catal.ReOrderLevel = catalogue_td[i].ReOrderLevel;
                    catal.ReOrderQty = catalogue_td[i].ReOrderQty;
                    catal.TotalQty = catalogue_td[i].TotalQty;
                    catal.UOM = catalogue_td[i].UOM;
                    catal_list.Add(catal);
                }

                return catal_list;
            }
            else
            {
                return null;
            }
        }

        //TT

        //public List<String> getBin()
        //{
        //    catalogue_td = tda.GetDataForBinId();
        //    if (catalogue_td != null)
        //    {
        //        List<String> bin_list = new List<string>();
        //        for (int i = 0; i < catalogue_td.Rows.Count; i++)
        //        {
        //            bin_list.Add(catalogue_td[i].BinId);
        //        }
        //        return bin_list;
        //    }
        //    else
        //    {
        //        return null;
        //    }
        //}


        public List<Catalogue> getCatalogueByBinId(String binId)
        {
            catalogue_td = tda.GetDataByBinId(binId);
            if (catalogue_td != null)
            {
                catal_list = new List<Catalogue>();
                for (int i = 0; i < catalogue_td.Rows.Count; i++)
                {
                    Catalogue catal = new Catalogue();
                    catal.BinId = catalogue_td[i].BinId;
                    catal.CategoryId = catalogue_td[i].CategoryId;
                    catal.Description = catalogue_td[i].Description;
                    catal.ItemId = catalogue_td[i].ItemId;
                    catal.ReOrderLevel = catalogue_td[i].ReOrderLevel;
                    catal.ReOrderQty = catalogue_td[i].ReOrderQty;
                    catal.TotalQty = catalogue_td[i].TotalQty;
                    catal.UOM = catalogue_td[i].UOM;
                    catal_list.Add(catal);
                }

                return catal_list;
            }
            else
            {
                return null;
            }
        }

        public List<Catalogue> getCatalogueByDesc(String desc)
        {
            catalogue_td = tda.GetDataByDesc("%" + desc + "%");
            if (catalogue_td != null)
            {
                catal_list = new List<Catalogue>();
                for (int i = 0; i < catalogue_td.Rows.Count; i++)
                {
                    Catalogue catal = new Catalogue();
                    catal.BinId = catalogue_td[i].BinId;
                    catal.CategoryId = catalogue_td[i].CategoryId;
                    catal.Description = catalogue_td[i].Description;
                    catal.ItemId = catalogue_td[i].ItemId;
                    catal.ReOrderLevel = catalogue_td[i].ReOrderLevel;
                    catal.ReOrderQty = catalogue_td[i].ReOrderQty;
                    catal.TotalQty = catalogue_td[i].TotalQty;
                    catal.UOM = catalogue_td[i].UOM;
                    catal_list.Add(catal);
                }

                return catal_list;
            }
            else
            {
                return null;
            }
        }

        public List<Catalogue> getCatalogueByDescAndCategory(String desc , int categoryId)
        {
            catalogue_td = tda.GetDataByDescAndCategory("%" + desc + "%",categoryId);
            if (catalogue_td != null)
            {
                catal_list = new List<Catalogue>();
                for (int i = 0; i < catalogue_td.Rows.Count; i++)
                {
                    Catalogue catal = new Catalogue();
                    catal.BinId = catalogue_td[i].BinId;
                    catal.CategoryId = catalogue_td[i].CategoryId;
                    catal.Description = catalogue_td[i].Description;
                    catal.ItemId = catalogue_td[i].ItemId;
                    catal.ReOrderLevel = catalogue_td[i].ReOrderLevel;
                    catal.ReOrderQty = catalogue_td[i].ReOrderQty;
                    catal.TotalQty = catalogue_td[i].TotalQty;
                    catal.UOM = catalogue_td[i].UOM;
                    catal_list.Add(catal);
                }

                return catal_list;
            }
            else
            {
                return null;
            }
        }


        public List<Catalogue> getCatalogueByBinAndCategory(String bin, int categoryId)
        {
            catalogue_td = tda.GetDataByBinAndCategory(bin, categoryId);
            if (catalogue_td != null)
            {
                catal_list = new List<Catalogue>();
                for (int i = 0; i < catalogue_td.Rows.Count; i++)
                {
                    Catalogue catal = new Catalogue();
                    catal.BinId = catalogue_td[i].BinId;
                    catal.CategoryId = catalogue_td[i].CategoryId;
                    catal.Description = catalogue_td[i].Description;
                    catal.ItemId = catalogue_td[i].ItemId;
                    catal.ReOrderLevel = catalogue_td[i].ReOrderLevel;
                    catal.ReOrderQty = catalogue_td[i].ReOrderQty;
                    catal.TotalQty = catalogue_td[i].TotalQty;
                    catal.UOM = catalogue_td[i].UOM;
                    catal_list.Add(catal);
                }

                return catal_list;
            }
            else
            {
                return null;
            }
        }

        public List<Catalogue> getCatalogueByBinAndDesc(String bin, String desc)
        {
            catalogue_td = tda.GetDataByBinAndDesc(bin, "%"+desc+"%");
            if (catalogue_td != null)
            {
                catal_list = new List<Catalogue>();
                for (int i = 0; i < catalogue_td.Rows.Count; i++)
                {
                    Catalogue catal = new Catalogue();
                    catal.BinId = catalogue_td[i].BinId;
                    catal.CategoryId = catalogue_td[i].CategoryId;
                    catal.Description = catalogue_td[i].Description;
                    catal.ItemId = catalogue_td[i].ItemId;
                    catal.ReOrderLevel = catalogue_td[i].ReOrderLevel;
                    catal.ReOrderQty = catalogue_td[i].ReOrderQty;
                    catal.TotalQty = catalogue_td[i].TotalQty;
                    catal.UOM = catalogue_td[i].UOM;
                    catal_list.Add(catal);
                }

                return catal_list;
            }
            else
            {
                return null;
            }
        }

        public List<Catalogue> getCatalogueByBinAndCategoryAndDesc(String bin,int categoryId, String desc)
        {
            catalogue_td = tda.GetDataByBinAndCategoryAndDesc(bin, categoryId, "%" + desc + "%");
            if (catalogue_td != null)
            {
                catal_list = new List<Catalogue>();
                for (int i = 0; i < catalogue_td.Rows.Count; i++)
                {
                    Catalogue catal = new Catalogue();
                    catal.BinId = catalogue_td[i].BinId;
                    catal.CategoryId = catalogue_td[i].CategoryId;
                    catal.Description = catalogue_td[i].Description;
                    catal.ItemId = catalogue_td[i].ItemId;
                    catal.ReOrderLevel = catalogue_td[i].ReOrderLevel;
                    catal.ReOrderQty = catalogue_td[i].ReOrderQty;
                    catal.TotalQty = catalogue_td[i].TotalQty;
                    catal.UOM = catalogue_td[i].UOM;
                    catal_list.Add(catal);
                }

                return catal_list;
            }
            else
            {
                return null;
            }
        }
        ///////////////////////////////////////////////////////////////

        //Category
        public List<Category> selectAllCategory()
        {
            categ_list = new List<Category>();
            for (int i = 0; i < ds.Category.Rows.Count; i++)
            {
                Category categ = new Category();
                categ.CategoryId = ds.Category[i].CategoryId;
                categ.CategoryName = ds.Category[i].CategoryName;

                //Catalogue
                catal_list = selectCatalogueByCategoryId(ds.Category[i].CategoryId);
                categ.Catalogue_list = catal_list;
                categ_list.Add(categ);
            }
            return categ_list;
        }

        //return data table
        public DataTable selectAllCategory_DT()
        {
            return ds.Category;
        }


        public int addCategory(Category category)
        {
            SSISDataSet.CategoryRow newRow = ds.Category.NewCategoryRow();
            newRow.CategoryId = category.CategoryId;
            newRow.CategoryName = category.CategoryName;

            ds.Category.Rows.Add(newRow);
            return categorytda.Update(ds.Category);
        }

        //private int getIndex(int Id)
        //{
        //    categorytda.Fill(ds.Category);
        //    SSISDataSet.CategoryRow row = ds.Category.FindByCategoryId(Id);
        //    return ds.Category.Rows.IndexOf(row);
        //}

        private SSISDataSet.CategoryRow getRowById(int id)
        {
            return ds.Category.FindByCategoryId(id);
        }

        public int updateCategory(Category category, int Id)
        {
            SSISDataSet.CategoryRow row = getRowById(Id);
            if (row != null)
            {
                row.CategoryId = category.CategoryId;
                row.CategoryName = category.CategoryName;
            }

            return categorytda.Update(ds.Category);
        }

        public int deleteCategory(int Id)
        {
            
            SSISDataSet.CategoryRow row = getRowById(Id);
            if (row != null)
            {
                ds.Category.Rows.Remove(row);
            }
            return categorytda.Update(ds.Category);
        }

        public Category selectCategory(int Id)
        {
            SSISDataSet.CategoryRow r = getRowById(Id);
            if (r != null)
            {
                Category categ = new Category();
                categ.CategoryId = r.CategoryId;
                categ.CategoryName = r.CategoryName;

                //Catalogue
                catal_list = selectCatalogueByCategoryId(r.CategoryId);
                categ.Catalogue_list = catal_list;
                return categ;
            }
            else
            {
                return null;
            }
        }
    }
}
